优化案例：CASE WHEN进行 SQL 改写优化

2023-12-15 20:12| 来源: 网络整理| 查看: 265

导读

今天给大家分享一个通过SQL改写而独辟蹊径的SQL优化案例。

待优化场景

发现SLOW QUERY LOG中有下面这样一条记录：

... # Query_time: 59.503827 Lock_time: 0.000198 Rows_sent: 641227 Rows_examined: 13442472 Rows_affected: 0 ... select uid,sum(power) powerup from t1 where date>='2017-03-31' and UNIX_TIMESTAMP(STR_TO_DATE(concat(date,' ',hour),'%Y-%m-%d %H'))>=1490965200 and UNIX_TIMESTAMP(STR_TO_DATE(concat(date,' ',hour),'%Y-%m-%d %H')) EXPLAIN select uid,sum(power) powerup from t1 where date>='2017-03-31' and UNIX_TIMESTAMP(STR_TO_DATE(concat(date,' ',hour),'%Y-%m-%d %H'))>=1490965200 and UNIX_TIMESTAMP(STR_TO_DATE(concat(date,' ',hour),'%Y-%m-%d %H'))='2017-03-24 13:00' then power else '0' end as powerup, case when concat(date,' ',hour) < '2017-03-25 13:00' then power else '0' end as powerup1 from t1 where date>='2017-03-24' and date ALTER TABLE t1 ADD INDEX idx_uid(uid); [email protected][myDB]> EXPLAIN select uid,sum(powerup+powerup1) from ( select uid, case when concat(date,' ',hour) >='2017-03-24 13:00' then power else '0' end as powerup, case when concat(date,' ',hour) < '2017-03-25 13:00' then power else '0' end as powerup1 from t1 where date>='2017-03-24' and date

【本文地址】

公司简介

联系我们